package com.apes.scm.masterdata.merchant.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.merchant.model.Merchant;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author zhangwencong
 * @create 2022-04-28 15:08
 */
public interface MerchantRepository extends CustomRepository<Merchant, String> {

    @Query(value = "select A.NAME,\n" +
            "       (selectLISTAGG(C.NAME || chr(47) || D.MOBILE_NUMBER || (case when a.NOTE is null then '' else chr(47) || D.NOTE end ), ' || ') within group(order by C.NAME || chr(47) || D.MOBILE_NUMBER || chr(47) || a.NOTE)\n" +
            "        from mas_contacts D,\n" +
            "             MAS_PARTY_ROLE B,\n" +
            "             MAS_PARTY C\n" +
            "        where D.STORE_ID = A.ID\n" +
            "          and D.CONTACT_ID = b.ID\n" +
            "          AND C.ID = B.PARTY_ID)as contacts\n" +
            "       from MAS_STORE A" +
            "       where a.MERCHANT_ID = :merchantId" +
            "", nativeQuery = true)
    List<Map> findAllStoreInfoByMerchant(String merchantId);

}
